import pandas as pd
import matplotlib.pyplot as plt
import os

# 配置常量
FILE_PATH = 'd:/民大实训/git-mcp-test/project/夏宇慧/FhjlViewDD.xlsx'
DATE_COLUMN = '创建时间'
CARGO_TYPE_COLUMN = '货品'
CUSTOMER_COLUMN = '客户'
DEPARTURE_COLUMN = '发货地'
LICENSE_PLATE_COLUMN = '车辆'
FREIGHT_COLUMN = '净重'

# 配置 matplotlib 支持中文显示
plt.rcParams['font.sans-serif'] = ['Microsoft YaHei']
plt.rcParams['axes.unicode_minus'] = False

# 定义输出目录
OUTPUT_DIR = 'd:/民大实训/git-mcp-test/project/夏宇慧'
if not os.path.exists(OUTPUT_DIR):
    os.makedirs(OUTPUT_DIR)

def load_data():
    """加载数据并筛选 6 月份的数据"""
    df = pd.read_excel(FILE_PATH)
    df[DATE_COLUMN] = pd.to_datetime(df[DATE_COLUMN])
    return df[df[DATE_COLUMN].dt.month == 6]

def daily_mineral_trend(data):
    """统计 6 月份每日矿粉货运量的日趋势并绘制柱状图"""
    mineral_data = data[data[CARGO_TYPE_COLUMN] == '矿粉']
    daily_total = mineral_data.groupby(data[DATE_COLUMN].dt.day)[FREIGHT_COLUMN].sum()
    ax = daily_total.plot(kind='bar', title='6 月份每日矿粉货运量趋势')
    plt.xlabel('日期')
    plt.ylabel('货运量')
    # 添加数据标签
    for p in ax.patches:
        ax.annotate(format(p.get_height(), '.0f'),
                    (p.get_x() + p.get_width() / 2., p.get_height()),
                    ha = 'center', va = 'center',
                    xytext = (0, 9),
                    textcoords = 'offset points')
    plt.tight_layout()
    plt.savefig(os.path.join(OUTPUT_DIR, '(a)6月份每日矿粉货运量趋势图.png'))
    plt.close()
    return daily_total


def daily_cement_trend(data):
    """统计 6 月份每日水泥货运量的日趋势并绘制柱状图"""
    cement_data = data[data[CARGO_TYPE_COLUMN] == '水泥']
    daily_total = cement_data.groupby(data[DATE_COLUMN].dt.day)[FREIGHT_COLUMN].sum()
    ax = daily_total.plot(kind='bar', title='6 月份每日水泥货运量趋势')
    plt.xlabel('日期')
    plt.ylabel('货运量')
    # 添加数据标签
    for p in ax.patches:
        ax.annotate(format(p.get_height(), '.0f'),
                    (p.get_x() + p.get_width() / 2., p.get_height()),
                    ha = 'center', va = 'center',
                    xytext = (0, 9),
                    textcoords = 'offset points')
    plt.tight_layout()
    plt.savefig(os.path.join(OUTPUT_DIR, '(b)6月份每日水泥货运量趋势图.png'))
    plt.close()
    return daily_total


def customer_demand(data):
    """统计每个客户的 6 月份的货运需求量，并按大到小排序"""
    demand = data.groupby(CUSTOMER_COLUMN)[FREIGHT_COLUMN].sum()
    sorted_demand = demand.sort_values(ascending=False)
    sorted_demand.to_csv(os.path.join(OUTPUT_DIR, 'june_customer_demand.csv'))
    return sorted_demand


def departure_total(data):
    """统计 6 月份各发货地的发货总量，并绘制柱状图"""
    total = data.groupby(DEPARTURE_COLUMN)[FREIGHT_COLUMN].sum()
    ax = total.plot(kind='bar', title='6 月份各发货地发货总量')
    plt.xlabel('发货地')
    plt.ylabel('发货总量')
    # 添加数据标签
    for p in ax.patches:
        ax.annotate(format(p.get_height(), '.0f'),
                    (p.get_x() + p.get_width() / 2., p.get_height()),
                    ha = 'center', va = 'center',
                    xytext = (0, 9),
                    textcoords = 'offset points')
    plt.tight_layout()
    plt.savefig(os.path.join(OUTPUT_DIR, '(d)6月份各发货地发货总量统计图.png'))
    plt.close()
    return total

def license_plate_total(data):
    """统计 6 月份各车牌号的总货运量，并按大到小排序"""
    total = data.groupby(LICENSE_PLATE_COLUMN)[FREIGHT_COLUMN].sum()
    sorted_total = total.sort_values(ascending=False)
    sorted_total.to_csv(os.path.join(OUTPUT_DIR, 'june_license_plate_total.csv'))
    return sorted_total

def cargo_type_distribution(data):
    """统计 6 月份各类货品的货运量占比并绘制饼图"""
    cargo_distribution = data.groupby(CARGO_TYPE_COLUMN)[FREIGHT_COLUMN].sum()
    ax = cargo_distribution.plot(kind='pie',
                                autopct='%1.1f%%',
                                ylabel='',
                                title='6 月份各类货品货运量占比')
    plt.tight_layout()
    plt.savefig(os.path.join(OUTPUT_DIR, '(e)6月份各类货品货运量占比.png'))
    plt.close()
    return cargo_distribution


def daily_total_freight(data):
    """统计 6 月份每日总货运量趋势并绘制折线图"""
    daily_total = data.groupby(data[DATE_COLUMN].dt.day)[FREIGHT_COLUMN].sum()
    ax = daily_total.plot(kind='line', marker='o', title='6 月份每日总货运量趋势')
    plt.xlabel('日期')
    plt.ylabel('总货运量')
    for x, y in zip(daily_total.index, daily_total):
        ax.annotate(str(y), (x, y))
    plt.tight_layout()
    plt.savefig(os.path.join(OUTPUT_DIR, '(f)6月份每日总货运量趋势.png'))
    plt.close()
    return daily_total

def daily_mineral_analysis(data):
    """对每日矿粉数据进行统计分析并保存结果"""
    mineral_data = data[data[CARGO_TYPE_COLUMN] == '矿粉']
    daily_total = mineral_data.groupby(data[DATE_COLUMN].dt.day)[FREIGHT_COLUMN].sum()
    analysis = daily_total.describe()
    analysis.to_csv(os.path.join(OUTPUT_DIR, 'june_daily_mineral_analysis.csv'))
    return analysis

def comprehensive_analysis(data):
    """对数据进行综合统计分析并保存结果"""
    # 统计数据总行数
    total_rows = len(data)
    
    # 统计不同货品数量
    cargo_types = data[CARGO_TYPE_COLUMN].nunique()
    
    # 统计活跃客户数量
    active_customers = data[CUSTOMER_COLUMN].nunique()
    
    # 统计总货运量
    total_freight = data[FREIGHT_COLUMN].sum()
    
    analysis_result = {
        '数据总行数': total_rows,
        '不同货品数量': cargo_types,
        '活跃客户数量': active_customers,
        '总货运量': total_freight
    }
    
    result_df = pd.DataFrame.from_dict(analysis_result, orient='index')
    result_df.to_csv(os.path.join(OUTPUT_DIR, 'comprehensive_analysis.csv'))
    return result_df

if __name__ == '__main__':
    try:
        data = load_data()
        daily_mineral_trend(data)
        daily_cement_trend(data)
        customer_demand(data)
        departure_total(data)
        license_plate_total(data)
        cargo_type_distribution(data)
        daily_total_freight(data)
        daily_mineral_analysis(data)
        comprehensive_analysis(data)
        print('所有统计和可视化任务已完成。')
    except Exception as e:
        print(f'处理过程中出现错误: {e}')